package com.and9.mchen.mweather.cw.demo;

import groovy.mock.interceptor.Demand;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.and9.mchen.mweather.cw.db.DBManager;
import com.and9.mchen.mweather.cw.utils.Cn2SpellUtil;

public class FetchMain {

	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws SQLException {
	
		//FetchMain.saveProvinceInDB();
		//FetchMain.saveCityByProvinceInDB();
		//FetchMain.updateLocalCode();
		//FetchMain.saveOtherCityInDB();
		//deleteCode();
	}
	
	public static void saveOtherCityInDB() throws SQLException{
		List<Local> list=GXMLData.getOtherCityTwon();
		QueryRunner qr=new QueryRunner(DBManager.getDataSource());
		for(Local local : list){
			String sql="SELECT id from local WHERE localCode=? ";
			Local temp=qr.query(sql, new BeanHandler(Local.class),local.getLocalCode());
			System.out.println(temp.getId());
			local.setId(temp.getId());
			Object[][] params=new Object[local.getChildren().size()][6];
			for(int i=0; i< local.getChildren().size(); i++){
				params[i][0]=local.getChildren().get(i).getName();
				String py=Cn2SpellUtil.converterToSpell(local.getChildren().get(i).getName());
				params[i][1]=py;
				params[i][2]=local.getId();
				params[i][3]="citytown";
				String nameEn=py;
				nameEn=nameEn.toUpperCase().charAt(0)+nameEn.substring(1);
				params[i][4]=nameEn;
				params[i][5]=local.getChildren().get(i).getLocalCode();
			}
			sql = "insert local(name,pinyin,parentId,type,nameEN, localCode) values(?,?,?,?,?, ?)";
			int[] result=qr.batch(sql, params);
			System.out.println(local.getChildren().size());
		}
	}
	
	public static void saveProvinceInDB() throws SQLException{
		Local local=GXMLData.getProvinceLocal();
		String sql = "insert local(name,pinyin,parentId) values(?,?,?)";
		QueryRunner queryRunner=new QueryRunner(DBManager.getDataSource());
		Object[][] params=new Object[local.getChildren().size()][3];
		for(int i=0; i< local.getChildren().size(); i++){
			params[i][0]=local.getChildren().get(i).getName();
			params[i][1]=local.getChildren().get(i).getPinyin();
			params[i][2]=local.getId();
		}
		int[] result=queryRunner.batch(sql, params);
		
	}
	
	public static void saveCityByProvinceInDB(String py, long id) throws SQLException{
		
		Local local=GXMLData.getProvinceWithCity(py, id);
		for(Local child : local.getChildren()){
			System.out.println(child.getName());
		}
		String sql = "insert local(name,pinyin,parentId,type,nameEN) values(?,?,?,?,?)";
		QueryRunner queryRunner=new QueryRunner(DBManager.getDataSource());
		Object[][] params=new Object[local.getChildren().size()][5];
		for(int i=0; i< local.getChildren().size(); i++){
			params[i][0]=local.getChildren().get(i).getName();
			params[i][1]=local.getChildren().get(i).getPinyin();
			params[i][2]=local.getId();
			params[i][3]="citytown";
			String nameEn=local.getChildren().get(i).getPinyin();
			nameEn=nameEn.toUpperCase().charAt(0)+nameEn.substring(1);
			params[i][4]=nameEn;
		}
		int[] result=queryRunner.batch(sql, params);
	}

	public static void updateLocalCode() throws SQLException{
		List<Local> list=GXMLData.getCityByProvince();
		String sql = "UPDATE local SET localCode=? WHERE name=? && type!='province' && type!='sar' && type!='municipality'";
		QueryRunner queryRunner=new QueryRunner(DBManager.getDataSource());
		for(Local local : list){
			Object[][] params=new Object[local.getChildren().size()][2];
			for(int i=0; i < local.getChildren().size(); i++){
				params[i][0]=local.getChildren().get(i).getLocalCode();
				params[i][1]=local.getChildren().get(i).getName();
			}
			int[] result=queryRunner.batch(sql, params);
		}
	}
	
	
	public static void deleteCode() throws SQLException{
		QueryRunner qr=new QueryRunner(DBManager.getDataSource());
			String sql="SELECT DISTINCT  a.id FROM `local` a, `local` b WHERE a.localCode=b.localCode AND a.localCode!=0 AND a.id!=b.id AND a.id >=400 ORDER BY a.id";
			List<Local> list=qr.query(sql, new BeanListHandler(Local.class));
			//System.out.println(list.get(0).getId());
			sql="DELETE FROM `local` WHERE `local`.id=?";
			Object[][] params=new Object[list.size()][1];
			for(int i=0 ;i<list.size(); i++){
				params[i][0]=list.get(i).getId();
			}
			int[] result=qr.batch(sql, params);
			
	}
}
